package com.pineapple.module.core.graph.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import com.pineapple.module.core.graph.domain.Graph;
import com.pineapple.module.core.graph.domain.GraphCriteria;

public class GraphDAO {

	protected List<Graph> select(GraphCriteria criteria,Connection conn) throws Exception{

		List<Graph> lstGraph = new ArrayList<Graph>();
		Graph graph = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT  ");
		sql.append("harvest.harvest_value, ");
		sql.append("area.area_name, ");
		sql.append("planting.planting_date ");
		sql.append("FROM ");
		sql.append("planting ");
		sql.append("Inner Join area ON planting.area_id = area.area_id ");
		sql.append("Inner Join harvest ON harvest.harvest_area = planting.planting_id ");
		sql.append("WHERE 1=1 ");
		if(criteria.getGraphDateFrom()!=null && !criteria.getGraphDateFrom().equals("")){
			sql.append("AND harvest.harvest_date  >='"+dateForInsert("01/01/"+criteria.getGraphDateFrom())+"' ");
		}
		if(criteria.getGraphDateTo()!=null && !criteria.getGraphDateTo().equals("")){
			sql.append("AND harvest.harvest_date  <='"+dateForInsert("31/12/"+criteria.getGraphDateTo())+"' ");
		}

		System.out.println(sql);

		try(Statement stmt = conn.createStatement()){
			ResultSet rst = stmt.executeQuery(sql.toString());
			while(rst.next()) {
               graph  = new Graph();
               graph.setAreaName(rst.getString("area_name"));
               graph.setValue(rst.getString("harvest_value"));
               graph.setPlantingDate(rst.getString("planting_date"));
               lstGraph.add(graph);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstGraph;
	}

	public static String convertToString(Date date, String format) {
		if (date != null) {
			SimpleDateFormat formatDate = new SimpleDateFormat(format.toString());

			StringBuilder fdate = new StringBuilder(formatDate.format(date));
			return fdate.toString();
		} else {
			return "";
		}
	}


	public static String dateForInsert(String date){
		String returnValue = null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("en", "EN"));
		try {
			if(date != null && !date.trim().equals("")){
				returnValue = convertToString(sdf.parse(date),"yyyy-MM-dd");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
}
